﻿-- create following views for stock balance chart
CREATE OR REPLACE VIEW "V_GTIN_HF_STOCK_POLICY_HELPER" AS 
 SELECT DISTINCT hfb."HEALTH_FACILITY_CODE",
    i."NAME",
    hfb."SAFETY_STOCK"
   FROM "GTIN_HF_STOCK_POLICY" hfb
     JOIN "ITEM_LOT" il ON hfb."GTIN" = il."GTIN"
     JOIN "ITEM" i ON i."ID" = il."ITEM_ID";

CREATE OR REPLACE VIEW "V_HEALTH_FACILITY_BALANCE_HELPER" AS 
 SELECT hfb."HEALTH_FACILITY_CODE",
    i."NAME",
    sum(hfb."BALANCE") AS sum
   FROM "HEALTH_FACILITY_BALANCE" hfb
     JOIN "ITEM_MANUFACTURER" im USING ("GTIN")
     JOIN "ITEM_LOT" il USING ("GTIN", "LOT_NUMBER")
     JOIN "ITEM" i ON i."ID" = il."ITEM_ID"
  WHERE im."IS_ACTIVE" = true AND il."IS_ACTIVE" = true AND i."IS_ACTIVE" = true
  GROUP BY hfb."HEALTH_FACILITY_CODE", i."NAME";

  
-- create view for monthly consumption report
  CREATE OR REPLACE VIEW facility_monthly_consumption AS 
 SELECT date_trunc('MONTH'::text, "ITEM_TRANSACTION"."TRANSACTION_DATE"::timestamp with time zone) AS "DATE",
    "ITEM_TRANSACTION"."HEALTH_FACILITY_CODE",
    "ITEM_TRANSACTION"."GTIN",
    sum(- "ITEM_TRANSACTION"."TRANSACTION_QTY_IN_BASE_UOM") AS consumption
   FROM "ITEM_TRANSACTION"
  WHERE "ITEM_TRANSACTION"."TRANSACTION_TYPE_ID" = 5
  GROUP BY date_trunc('MONTH'::text, "ITEM_TRANSACTION"."TRANSACTION_DATE"::timestamp with time zone), "ITEM_TRANSACTION"."HEALTH_FACILITY_CODE", "ITEM_TRANSACTION"."GTIN";


-- create indexes 
CREATE INDEX 
   ON "GTIN_HF_STOCK_POLICY" ("HEALTH_FACILITY_CODE" ASC NULLS LAST);

CREATE INDEX "HEALTH_FACILITY_VACCINATION_POINT_idx"
  ON "HEALTH_FACILITY"
  USING btree
  ("VACCINATION_POINT");

DROP INDEX "CHILD_HF_IDX";
DROP INDEX "CHILD_ID";


CREATE UNIQUE INDEX 
   ON "CHILD" ("BARCODE_ID" ASC NULLS LAST) where "BARCODE_ID" <> '';

CREATE INDEX 
   ON "ITEM_LOT" ("GTIN" ASC NULLS LAST);
CREATE INDEX 
   ON "ITEM_LOT" ("ITEM_ID" ASC NULLS LAST);

-- recreate missing foreign key constraints
ALTER TABLE "CHILD"
  ADD FOREIGN KEY ("BIRTHPLACE_ID") REFERENCES "BIRTHPLACE" ("ID") ON UPDATE NO ACTION ON DELETE NO ACTION;

  ALTER TABLE "CHILD"
  ADD FOREIGN KEY ("DOMICILE_ID") REFERENCES "PLACE" ("ID") ON UPDATE NO ACTION ON DELETE NO ACTION;

-- ALTER TABLE "CHILD"
--   ADD FOREIGN KEY ("COMMUNITY_ID") REFERENCES "COMMUNITY" ("ID") ON UPDATE NO ACTION ON DELETE NO ACTION;

  ALTER TABLE "DOSE"
  ADD FOREIGN KEY ("FROM_AGE_DEFINITION_ID") REFERENCES "AGE_DEFINITIONS" ("ID") ON UPDATE NO ACTION ON DELETE NO ACTION;
  
ALTER TABLE "DOSE"
  ADD FOREIGN KEY ("TO_AGE_DEFINITION_ID") REFERENCES "AGE_DEFINITIONS" ("ID") ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE "HEALTH_FACILITY"
  ADD FOREIGN KEY ("TYPE_ID") REFERENCES "HEALTH_FACILITY_TYPE" ("ID") ON UPDATE NO ACTION ON DELETE NO ACTION;

-- add row on BIRTHPLACE table
INSERT INTO "BIRTHPLACE" VALUES (0, 'Unknown', NULL, TRUE, CURRENT_TIMESTAMP, 1);